﻿Imports System
Imports System.Data
Imports System.Xml

Public MustInherit Class DataAccessLayerBaseClass

#Region "Membres"
    Private sConnectionString As String
    Private connection As IDbConnection
    Private command As IDbCommand
    Private transaction As IDbTransaction
    Private da As IDbDataAdapter
#End Region

#Region "Propriétés"

    ''' <summary>
    ''' Gets|Sets la ConnectionString pour ouvrir la base de données.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property ConnectionString() As String
        Get
            ' make sure conection string is not empty
            If sConnectionString = String.Empty OrElse sConnectionString.Length = 0 Then
                Throw New ArgumentException("La chaîne de connexion de la base de données est invalide.")
            End If
            Return sConnectionString
        End Get
        Set(ByVal value As String)
            sConnectionString = value
        End Set
    End Property

#End Region

#Region "Méthodes"

    ''' <summary>
    ''' Préparation de la commande à exécuter dans la base de données
    ''' </summary>
    ''' <param name="commandType">Type de commande</param>
    ''' <param name="commandText">La commande SQL</param>
    ''' <param name="commandParameters">Les paramètres pour la commande</param>
    ''' <remarks></remarks>
    Private Sub PrepareCommand(ByVal commandType As CommandType, ByVal commandText As String, ByVal commandParameters As List(Of IDataParameter))

        'Faire la connexion si elle est inexistante
        If connection Is Nothing Then
            connection = GetDataProviderConnection()
            connection.ConnectionString = Me.ConnectionString
        End If

        'Ouvrir la connexion si elle n'est pas ouverte
        If connection.State <> ConnectionState.Open Then
            connection.Open()
        End If

        ' Initialiser l'objet Command 
        If command Is Nothing Then
            command = GeDataProviderCommand()
        End If

        command.Connection = connection
        command.CommandText = commandText
        command.CommandType = commandType

        If Not (transaction Is Nothing) Then
            command.Transaction = transaction
        End If

        If Not (commandParameters Is Nothing) Then
            Dim param As IDataParameter
            For Each param In commandParameters
                command.Parameters.Add(param)
            Next param
        End If
    End Sub

#Region "Abstract Function"

    '/ <summary>
    '/ Data provider specific implementation for accessing relational databases.
    '/ </summary>
    Public MustOverride Function GetDataProviderConnection() As IDbConnection

    '/ <summary>
    '/ Data provider specific implementation for executing SQL statement while connected to a data source.
    '/ </summary>
    Public MustOverride Function GeDataProviderCommand() As IDbCommand

    '/ <summary>
    '/ Data provider specific implementation for filling the DataSet.
    '/ </summary>
    Public MustOverride Function GetDataProviderDataAdapter() As IDbDataAdapter

#End Region

#Region "Database Transaction"

    ''' <summary>
    ''' Début d'une transaction dans la base de données
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub BeginTransaction()
        If Not (transaction Is Nothing) Then
            Return
        End If
        Try
            connection = GetDataProviderConnection()
            connection.ConnectionString = Me.ConnectionString
            connection.Open()
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
        Catch ex As Exception
            connection.Close()
            Throw ex
        End Try
    End Sub

    ''' <summary>
    ''' Commettre une transaction dans la base de données
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub CommitTransaction()
        If transaction Is Nothing Then
            Return
        End If

        Try
            'Commit transaction
            transaction.Commit()
        Catch ex As Exception
            'rollback transaction
            RollbackTransaction()
            Throw ex
        Finally
            connection.Close()
            transaction = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Rollback de la transaction en statut [Pending]
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub RollbackTransaction()
        If transaction Is Nothing Then
            Return
        End If
        Try
            transaction.Rollback()
        Catch ex As Exception

        Finally
            connection.Close()
            transaction = Nothing
        End Try
    End Sub

#End Region

#Region "ExecuteDataReader"

    Public Overloads Function ExecuteDataReader(ByVal commandText As String) As IDataReader
        Return Me.ExecuteDataReader(commandText, CommandType.Text, Nothing)
    End Function

    Public Overloads Function ExecuteDataReader(ByVal commandText As String, ByVal commandType As CommandType) As IDataReader
        Return Me.ExecuteDataReader(commandText, commandType, Nothing)
    End Function

    Public Overloads Function ExecuteDataReader(ByVal commandText As String, ByVal commandParameters As List(Of IDataParameter)) As IDataReader
        Return Me.ExecuteDataReader(commandText, CommandType.Text, commandParameters)
    End Function

    Public Overloads Function ExecuteDataReader(ByVal commandText As String, ByVal commandType As CommandType, ByVal commandParameters As List(Of IDataParameter)) As IDataReader
        Try
            PrepareCommand(commandType, commandText, commandParameters)

            Dim dr As IDataReader

            If transaction Is Nothing Then
                dr = command.ExecuteReader(CommandBehavior.CloseConnection)
            Else
                dr = command.ExecuteReader()
            End If
            Return dr
        Catch ex As Exception
            If transaction Is Nothing Then
                connection.Close()
                command.Dispose()
            Else
                RollbackTransaction()
            End If
            Throw ex
        End Try
    End Function

#End Region

#Region "ExecuteDataSet"

    Public Overloads Function ExecuteDataSet(ByVal commandText As String) As DataSet
        Return Me.ExecuteDataSet(commandText, CommandType.Text, Nothing)
    End Function

    Public Overloads Function ExecuteDataSet(ByVal commandText As String, ByVal commandType As CommandType) As DataSet
        Return Me.ExecuteDataSet(commandText, commandType, Nothing)
    End Function

    Public Overloads Function ExecuteDataSet(ByVal commandText As String, ByVal commandParameters As List(Of IDataParameter)) As DataSet
        Return Me.ExecuteDataSet(commandText, CommandType.Text, commandParameters)
    End Function

    Public Overloads Function ExecuteDataSet(ByVal commandText As String, ByVal commandType As CommandType, ByVal commandParameters As List(Of IDataParameter)) As DataSet
        Try
            PrepareCommand(commandType, commandText, commandParameters)

            'Dim da As IDbDataAdapter = GetDataProviderDataAdapter()

            Dim ds As New DataSet()

            da.SelectCommand = command

            da.Fill(ds)

            Return ds

        Catch ex As Exception
            If transaction Is Nothing Then
                connection.Close()
            Else
                RollbackTransaction()
            End If
            Throw ex
        Finally
            If transaction Is Nothing Then
                connection.Close()
                command.Dispose()
            End If
        End Try
    End Function

    'Public Overloads Function UpdateDataSet(ByVal ds As DataSet) As Boolean

    '    Try
    '        'Dim da As IDbDataAdapter = GetDataProviderDataAdapter()
    '        Dim cmb As New Odbc.OdbcCommandBuilder(da)

    '        cmb.ConflictOption = ConflictOption.CompareRowVersion

    '        Dim cmd As IDbCommand = cmb.GetDeleteCommand
    '        cmd.CommandText = "DELETE FROM RMS.dbo.BLKF WHERE ((INDEX = ?) AND (RUE = ?) AND ((? = 1 AND FILLER1 IS NULL) OR (FILLER1 = ?)) AND ((? = 1 AND TYPE IS NULL) OR (TYPE = ?)) AND ((? = 1 AND CENTAINE IS NULL) OR (CENTAINE = ?)) AND ((? = 1 AND DIZMIN IS NULL) OR (DIZMIN = ?)) AND ((? = 1 AND DIZMAX IS NULL) OR (DIZMAX = ?)) AND ((? = 1 AND LIEUPUBL IS NULL) OR (LIEUPUBL = ?)) AND ((? = 1 AND FILLER2 IS NULL) OR (FILLER2 = ?)) AND ((? = 1 AND PAIRIMPAIR IS NULL) OR (PAIRIMPAIR = ?)) AND ((? = 1 AND FLAGLP IS NULL) OR (FLAGLP = ?)) AND ((? = 1 AND FILLER3 IS NULL) OR (FILLER3 = ?)) AND ((? = 1 AND VILLE IS NULL) OR (VILLE = ?)) AND ((? = 1 AND ATOME IS NULL) OR (ATOME = ?)) AND ((? = 1 AND ZONE IS NULL) OR (ZONE = ?)) AND ((? = 1 AND AMBUL IS NULL) OR (AMBUL = ?)) AND ((? = 1 AND PERMIS IS NULL) OR (PERMIS = ?)) AND ((? = 1 AND FILLER4 IS NULL) OR (FILLER4 = ?)) AND ((? = 1 AND FILLER5 IS NULL) OR (FILLER5 = ?)) AND ((? = 1 AND COORDX IS NULL) OR (COORDX = ?)) AND ((? = 1 AND COORDY IS NULL) OR (COORDY = ?)) AND ((? = 1 AND PROP IS NULL) OR (PROP = ?)))"

    '        da.DeleteCommand = cmd

    '        If ds.HasChanges Then
    '            da.Update(ds)
    '        End If

    '        Return True

    '    Catch ex As Exception
    '        Throw ex
    '    End Try

    'End Function

#End Region

#Region "ExecuteQuery"

    Public Overloads Function ExecuteQuery(ByVal commandText As String) As Integer
        Return Me.ExecuteQuery(commandText, CommandType.Text, Nothing)
    End Function

    Public Overloads Function ExecuteQuery(ByVal commandText As String, ByVal commandType As CommandType) As Integer
        Return Me.ExecuteQuery(commandText, commandType, Nothing)
    End Function

    Public Overloads Function ExecuteQuery(ByVal commandText As String, ByVal commandParameters As List(Of IDataParameter)) As Integer
        Return Me.ExecuteQuery(commandText, CommandType.Text, commandParameters)
    End Function

    Public Overloads Function ExecuteQuery(ByVal commandText As String, ByVal commandType As CommandType, ByVal commandParameters As List(Of IDataParameter)) As Integer
        Try
            PrepareCommand(commandType, commandText, commandParameters)

            Dim intAffectedRows As Integer = command.ExecuteNonQuery()

            Return intAffectedRows
        Catch ex As Exception
            If Not (transaction Is Nothing) Then
                RollbackTransaction()
            End If
            Throw ex
        Finally
            If transaction Is Nothing Then
                connection.Close()
                command.Dispose()
            End If
        End Try
    End Function

#End Region

#Region "ExecuteScalar"

    Public Overloads Function ExecuteScalar(ByVal commandText As String) As Object
        Return Me.ExecuteScalar(commandText, CommandType.Text, Nothing)
    End Function

    Public Overloads Function ExecuteScalar(ByVal commandText As String, ByVal commandType As CommandType) As Object
        Return Me.ExecuteScalar(commandText, commandType, Nothing)
    End Function

    Public Overloads Function ExecuteScalar(ByVal commandText As String, ByVal commandParameters As List(Of IDataParameter)) As Object
        Return Me.ExecuteScalar(commandText, CommandType.Text, commandParameters)
    End Function

    Public Overloads Function ExecuteScalar(ByVal commandText As String, ByVal commandType As CommandType, ByVal commandParameters As List(Of IDataParameter)) As Object
        Try
            PrepareCommand(commandType, commandText, commandParameters)

            Dim objValue As Object = command.ExecuteScalar()

            If objValue IsNot System.DBNull.Value Then
                Return objValue
            Else
                Return Nothing
            End If
        Catch ex As Exception
            If Not (transaction Is Nothing) Then
                RollbackTransaction()
            End If
            Throw ex
        Finally
            If transaction Is Nothing Then
                connection.Close()
                command.Dispose()
            End If
        End Try
    End Function

#End Region

#End Region

#Region "Constructeur|Destructeur"

    ''' <summary>
    ''' Constructeur de la classe DataAccessSQLCe
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub New()
        da = GetDataProviderDataAdapter()
    End Sub

    ''' <summary>
    ''' Constructeur de la classe DataAccessSQLCe
    ''' </summary>
    ''' <param name="connectionString">Chaîne de connexion à la base de données</param>
    ''' <remarks></remarks>
    Public Sub New(ByVal connectionString As String)
        Me.ConnectionString = connectionString
    End Sub

#End Region

End Class
